
use sqlx::PgPool;

use crate::{AppError, AppState};

use chat_core:: Workspace;

impl AppState {
    pub async fn create_woekspace(&self,name: &str,id: u64) -> Result<Workspace,AppError> {
        let ws: Workspace = sqlx::query_as(
            r#"
            INSERT INTO workspaces (name, owner_id)
            VALUES ($1, $2)
            RETURNING id, name, owner_id, created_at
            "#,
        ).bind(name)
        .bind(id as i64)  
        .fetch_one(&self.pool)
        .await?;
    Ok(ws)      
    }
    pub async fn find_workspace_by_name(&self,name: &str) -> Result<Option<Workspace>,AppError> {
        let ws: Option<Workspace> = sqlx::query_as(
            r#"
            SELECT id, name, owner_id, created_at
            FROM workspaces
            WHERE name = $1
            "#,
        ).bind(name)
        .fetch_optional(&self.pool)
        .await?;
    Ok(ws)
    }
    // #[allow(dead_code)]
    // pub async fn fetch_chat_users(&self,id: u64) -> Result<Vec<ChatUser>,AppError> {
    //     let ws: Vec<ChatUser> = sqlx::query_as(
    //         r#"
    //         SELECT id, fullname, email
    //     FROM users
    //     WHERE ws_id = $1 order by id 
    //         "#,
    //     ).bind(id as i64)
    //     .fetch_all(&self.pool)
    //     .await?;
    // Ok(ws)
    // }
    #[allow(dead_code)]
    pub async fn find_workspace_by_id(&self,id: u64) -> Result<Option<Workspace>,AppError>{
        let ws = sqlx::query_as(
            r#"
            SELECT id, name, owner_id, created_at
            FROM workspaces
            WHERE id = $1
            "#
        ).bind(id as i64)
        .fetch_optional(&self.pool)
        .await?;
    Ok(ws)
    }
    pub async fn update_workspace_onwer(&self, id: u64,owner_id: u64) -> Result<Workspace,AppError> {
        let ws: Workspace = sqlx::query_as(
            r#"
            UPDATE workspaces
            SET owner_id = $1
            WHERE id = $2 and (SELECT ws_id FROM users WHERE id = $1) = $2
            RETURNING id, name, owner_id, created_at
            "#,
        ).bind(owner_id as i64)
        .bind(id as i64)
        .fetch_one(&self.pool)
        .await?;
    Ok(ws)
    }
}

// impl Workspace {
//     pub async fn update_onwer(&self, owner_id: u64,pool: &PgPool) -> Result<Workspace,AppError> {
//         let ws: Workspace = sqlx::query_as(
//             r#"
//             UPDATE workspaces
//             SET owner_id = $1
//             WHERE id = $2 and (SELECT ws_id FROM users WHERE id = $1) = $2
//             RETURNING id, name, owner_id, created_at
//             "#,
//         ).bind(owner_id as i64)
//         .bind(self.id)
//         .fetch_one(pool)
//         .await?;
//     Ok(ws)
//     }
// }
#[allow(dead_code)]
pub async fn db_test_for_workspace(pool: &PgPool) -> Result<Vec<Workspace>,AppError>{
    let ws = sqlx::query_as(
        r#"
        SELECT *
        FROM workspaces
        "#
    ).fetch_all(pool)
    .await?;
    Ok(ws)
}

#[cfg(test)]
mod tests {

    use crate::models::CreateUser;

    use super::*;
    use anyhow::{Ok, Result};

    #[tokio::test]
    async fn workspace_should_create_and_set_owner() -> Result<()> {
        // let tdb = TestPg::new(
        //     "postgres://postgres:123456@localhost:5432".to_string(),
        //     Path::new("../migrations"),
        // );
        let (_tdb,state) = AppState::new_for_test().await?;
        let ws = state.create_woekspace("test111111", 0).await.unwrap();
        let input = CreateUser::new(&ws.name,"Tyr Chen11", "tchen@acme.org11", "Hunter421");
        let user = state.create_user(&input).await.unwrap();

        assert_eq!(ws.name, "test111111");

        assert_eq!(user.ws_id, ws.id);

        let ws = state.update_workspace_onwer(ws.id as _,user.id as _).await.unwrap();

        assert_eq!(ws.owner_id, user.id);
        Ok(())
    }

    #[tokio::test]
    async fn workspace_should_find_by_name() -> Result<()> {
        // let url = "postgres://postgres:123456@localhost:5432/chat";
        // let pool = PgPool::connect(url).await?;
        let (_tdb,state) = AppState::new_for_test().await?;
        let _ws = state.create_woekspace("test", 0).await?;
        let ws = state.find_workspace_by_name("test").await?;

        assert_eq!(ws.unwrap().name, "test");
        Ok(())
    }

    #[tokio::test]
    async fn workspace_should_fetch_all_chat_users() -> Result<()> {
        // let url = "postgres://postgres:123456@localhost:5432/chat";
        // let pool = PgPool::connect(url).await?;
        let (_tdb,state) = AppState::new_for_test().await?;
        let ws = state.create_woekspace("test22112", 0).await?;
        let input = CreateUser::new(&ws.name,"Tyr Chen", "tchen@acme.org122", "Hunter42");
        let user1 =state.create_user(&input).await?;
        let input = CreateUser::new(&ws.name,"Alice Wang", "alice@acme.org122", "Hunter42");
        let user2 = state.create_user(&input).await?;

        let users = state.fetch_chat_users(ws.id as _).await?;
        assert_eq!(users.len(), 2);
        assert_eq!(users[0].id, user1.id);
        assert_eq!(users[1].id, user2.id);

        Ok(())
    }
    #[tokio::test]
    pub async fn db_test(){
        let url = "postgres://postgres:123456@localhost:5432/chat";
        let pool = PgPool::connect(url).await.unwrap();
        let wss = db_test_for_workspace(&pool).await.unwrap();
        print!("{:?}",wss);
    }
}

